/* 
 * File:   bdOper.cpp
 * Author: f10-kafedra
 * 
 * Created on 16 Октябрь 2011 г., 11:43
 */

#include "bdOper.h"

bdOper::bdOper() {
}

bdOper::~bdOper() {
}

bool bdOper::connectToBD()
{
    string connStr = boost::str(format("user=%s password=%s dbname=%s hostaddr=%s") % DBLOGIN % DBPASS % DBNAME % HOSTBD);
    db = PQconnectdb(connStr.c_str());
    
    if (PQstatus(db) == CONNECTION_BAD)
    {
        cout << "Unable connect to DB: " << endl; 
        //QMessageBox m;
        //m.setText(db.lastError().text());
        //m.exec();
        return false;
    }
    else
    {
        cout << "connected to " << HOSTBD << " % " << DBNAME << endl;
        return true;
    }
}
/*
bool bdOper::connectToBD()
{
    db = QSqlDatabase::addDatabase("QPSQL");
    
    // указание данных для подключения
    db.setHostName(HOSTBD);
    db.setDatabaseName(DBNAME);
    db.setUserName(DBLOGIN);
    db.setPassword(DBPASS);
    db.setPort(5432);

    cout << "try to connect to " << HOSTBD << endl;
    
    if (!db.open())
    {
        cout << "Unable connect to DB: " << db.lastError().text().toAscii().data() << endl;
        QMessageBox m;
        m.setText(db.lastError().text());
        m.exec();
        return false;
    }
    else
    {
        cout << "connected to " << HOSTBD << endl;
//        QMessageBox m;
//        m.setText("Connected");
//        m.exec();
        return true;
    }
    
}
**/

QStringList bdOper::getFilialsID()
{
    QStringList list;
    
    if (connectToBD() == true)
    {
        string query("SELECT \"filialID\" FROM \"ipList\" WHERE \"ip\" = '" + myIp.toAscii() + "'");
        PGresult* result = PQexec(db, query.c_str());
        
        getResult(&result, 1, &list);

        disconnect();
    }
    return list;
}

/*
QStringList bdOper::getFilialsID()
{
    QStringList list;
    
    if (connectToBD() == true)
    {
        QSqlQuery query;
        if (query.exec("SELECT \"filialID\" FROM \"ipList\" WHERE \"ip\" = '" + myIp + "'"))
        {
            while (query.next())
            {
                //cout << query.value(0).toString().toAscii().data() << endl;
                list << query.value(0).toString();
            }
        }
        else
        {
            queryError(query);
        }
        
        disconnect();
    }
    
    return list;
}*/

QStringList bdOper::getFilialNames(QStringList ids)
{
    QStringList list;
    if (connectToBD() == true)
    {
        foreach (QString id, ids)
        {
            string query = "SELECT \"filial\" FROM \"filials\" WHERE \"id\" = '" + id.toStdString() + "'";
            PGresult* result = PQexec(db, query.c_str());

            getResult(&result, 1, &list);
        }
        
        disconnect();
    }

    return list;
}

/*
QStringList bdOper::getFilialNames(QStringList ids)
{
    QStringList list;
    if (connectToBD() == true)
    {
        QSqlQuery query;
        
        foreach (QString id, ids)
        {
            if (query.exec("SELECT \"filial\" FROM \"filials\" WHERE \"id\" = '" + id + "'"))
            {
                while (query.next())
                {
                    QTextCodec::setCodecForCStrings(QTextCodec::codecForName("cp1251"));
                    //cout << query.value(0).toString().toAscii().data() << endl;
                    list << query.value(0).toString();
                    QTextCodec::setCodecForCStrings(QTextCodec::codecForName("UTF-8"));
                }
            }
            else
            {
                queryError(query);
            }
        }
        
        disconnect();
    }
    return list;
}*/

void bdOper::disconnect()
{
    PQfinish(db);
}
/*
void bdOper::disconnect()
{
    db.close();
}*/

void bdOper::queryError(PGresult* result)
{
    QTextCodec::setCodecForCStrings(QTextCodec::codecForName("cp1251"));
    cout << PQresultErrorMessage(result) << endl;
    QTextCodec::setCodecForCStrings(QTextCodec::codecForName("UTF-8"));
}
/*
void bdOper::queryError(QSqlQuery query)
{
    QTextCodec::setCodecForCStrings(QTextCodec::codecForName("cp1251"));
    cout << query.lastError().text().toAscii().data() << endl;
    
    QTextCodec::setCodecForCStrings(QTextCodec::codecForName("UTF-8"));
}*/

void bdOper::saveAccount(QString login, QString pass, QString ip)
{
    if (connectToBD())
    {
        string query = "INSERT INTO \"accounts\" VALUES ((SELECT COUNT(*) FROM \"accounts\"), '" + ip.toStdString() + "', '" + login.toStdString() + "', '" + pass.toStdString() + "');";
        PGresult* result = PQexec(db, query.c_str());
        
        if (PQresultStatus(result) != PGRES_COMMAND_OK)
        {
            queryError(result);
        }
        else
        {
            cout << "account saved\n" << "ip: " << ip.toAscii().data() << "\nlogin: " 
                                                << login.toAscii().data() << "\npass: " 
                                                << pass.toAscii().data() << endl;            
        }
        
        disconnect();
    }
}
/*
void bdOper::saveAccount(QString login, QString pass, QString ip)
{
    if (connectToBD())
    {
        QSqlQuery query;
        if (query.exec("INSERT INTO \"accounts\" VALUES ((SELECT COUNT(*) FROM \"accounts\"), '" + ip + "', '" + login + "', '" + pass + "');"))
        {
            cout << "account saved\n" << "ip: " << ip.toAscii().data() << "\nlogin: " 
                                                << login.toAscii().data() << "\npass: " 
                                                << pass.toAscii().data() << endl;
        }
        else
            queryError(query);
    
        disconnect();
    }
}*/

QStringList bdOper::checkAccountAvailable(QString ip)
{
    QStringList accountData;
    cout << "checikng account" << endl;
    
    if (connectToBD() == true)
    {
        string query = "SELECT login, pass FROM \"accounts\" WHERE \"ip\" = '" + ip.toStdString() + "';";
        PGresult* result = PQexec(db, query.c_str());
        
/*        if (PQresultStatus(result) != PGRES_TUPLES_OK)
        {
            queryError(result);
        }
        else
        {
            for (int i = 0; i < PQntuples(result); i++)
            {
                accountData << QString::fromAscii(PQgetvalue(result, i, 0));
                accountData << QString::fromAscii(PQgetvalue(result, i, 1));
            }
        }*/
    
        getResult(&result, 2, &accountData);
        
        disconnect();
    }
    
    return accountData;
}

/*
QStringList bdOper::checkAccountAvailable(QString ip)
{
    QStringList accountData;
    
    cout << "checikng account" << endl;
    if (connectToBD())
    {
        QSqlQuery query;
        if (query.exec("SELECT login, pass FROM \"accounts\" WHERE \"ip\" = '" + ip + "';"))
        {
//            cout << "amount: " << query.size() << endl;
//            cout << query.lastQuery().toAscii().data() << endl;
            while (query.next())
            {
//                cout << "login: " << query.value(0).toString().toAscii().data() << "\npass: " << query.value(1).toString().toAscii().data() << endl;
                accountData << query.value(0).toString() << query.value(1).toString();
            }
            
        }
        else
            queryError(query);
    
        disconnect();
    }
    return accountData;
}*/

void bdOper::getResult(PGresult** result, unsigned short int items, QStringList* container)
{
    if (PQresultStatus(*result) != PGRES_TUPLES_OK)
    {
        queryError(*result);
    }
    else
    {
        for (unsigned short int i = 0; i < PQntuples(*result); i++)
        {
            for (unsigned short int j = 0; j < items; j++)
                *container << QString::fromAscii(PQgetvalue(*result, i, j));
        }
    }
}